using System;
using System.Data;
using System.Data.SqlClient;
using Entity;

namespace SalesVMT
{
    public class UsersDB
    {

        public static DataSet GetList(out int ItemCount, string keyWord, int currentPage, int pageSize)
        {

            DataSet retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_GetList", dbConn);
            dbCmd.Parameters.AddWithValue("@KeyWord", keyWord);
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@RPP", pageSize);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;

        }
        public static bool Delete(int _Users_ID)
        {
            bool retVal = false;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_Delete", dbConn);
            dbCmd.Parameters.AddWithValue("@Users_ID", _Users_ID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static int Insert(UsersInfo _UsersInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_Insert", dbConn);
            dbCmd.Parameters.AddWithValue("@Users_Name", _UsersInfo.Users_Name);
            dbCmd.Parameters.AddWithValue("@Users_PassWord", _UsersInfo.Users_PassWord);
            dbCmd.Parameters.AddWithValue("@Users_FullName", _UsersInfo.Users_FullName);
            dbCmd.Parameters.AddWithValue("@Users_Address", _UsersInfo.Users_Address);
            dbCmd.Parameters.AddWithValue("@Users_Phone", _UsersInfo.Users_Phone);
            dbCmd.Parameters.AddWithValue("@Users_Email", _UsersInfo.Users_Email);
            dbCmd.Parameters.AddWithValue("@Users_Roles", _UsersInfo.Users_Roles);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool Update(UsersInfo _UsersInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_Update", dbConn);
            
            dbCmd.Parameters.AddWithValue("@Users_ID", _UsersInfo.Users_ID);
            dbCmd.Parameters.AddWithValue("@Users_Name", _UsersInfo.Users_Name);
            dbCmd.Parameters.AddWithValue("@Users_PassWord", _UsersInfo.Users_PassWord);
            dbCmd.Parameters.AddWithValue("@Users_FullName", _UsersInfo.Users_FullName);
            dbCmd.Parameters.AddWithValue("@Users_Address", _UsersInfo.Users_Address);
            dbCmd.Parameters.AddWithValue("@Users_Phone", _UsersInfo.Users_Phone);
            dbCmd.Parameters.AddWithValue("@Users_Email", _UsersInfo.Users_Email);
            dbCmd.Parameters.AddWithValue("@Users_Roles", _UsersInfo.Users_Roles);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool ChangePassword(int User_ID, string User_pwd)
        {
            bool retVal = false;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_ChangePassword", dbConn);

            dbCmd.Parameters.AddWithValue("@Users_ID", User_ID);
            dbCmd.Parameters.AddWithValue("@Users_PassWord", User_pwd);

            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static UsersInfo GetInfo(int _Users_ID)
        {
            UsersInfo retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_GetInfo", dbConn);
            dbCmd.Parameters.AddWithValue("@Users_ID", _Users_ID);
            dbCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new UsersInfo();
                    retVal.Users_ID = Convert.ToInt32(dr["Users_ID"]);
                    retVal.Users_Name = Convert.ToString(dr["Users_Name"]);
                    retVal.Users_PassWord = Convert.ToString(dr["Users_PassWord"]);
                    retVal.Users_FullName = Convert.ToString(dr["Users_FullName"]);
                    retVal.Users_Address = Convert.ToString(dr["Users_Address"]);
                    retVal.Users_Phone = Convert.ToString(dr["Users_Phone"]);
                    retVal.Users_Email = Convert.ToString(dr["Users_Email"]);
                    retVal.Users_Roles = Convert.ToString(dr["Users_Roles"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public static UsersInfo GetInfoByUsersName(string _Users_Name)
        {
            UsersInfo retVal = null;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_GetInfoByUsersName", dbConn);
            dbCmd.Parameters.AddWithValue("@Users_Name", _Users_Name);
            dbCmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = null;

            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new UsersInfo();
                    retVal.Users_ID = Convert.ToInt32(dr["Users_ID"]);
                    retVal.Users_Name = Convert.ToString(dr["Users_Name"]);
                    retVal.Users_PassWord = Convert.ToString(dr["Users_PassWord"]);
                    retVal.Users_FullName = Convert.ToString(dr["Users_FullName"]);
                    retVal.Users_Address = Convert.ToString(dr["Users_Address"]);
                    retVal.Users_Phone = Convert.ToString(dr["Users_Phone"]);
                    retVal.Users_Email = Convert.ToString(dr["Users_Email"]);
                    retVal.Users_Roles = Convert.ToString(dr["Users_Roles"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public static int Using(int UsersID)
        {
            int retVal = 0;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();
            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Users_ID", UsersID);

            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool Exist(int UsersID, string UsersName)
        {
            bool retVal = false;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();

            SqlCommand dbCmd = new SqlCommand("SalesVMT_Users_Exist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Users_Name", UsersName);
            dbCmd.Parameters.AddWithValue("@Users_ID", UsersID);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)

                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static bool CheckValidate(string Users_Name, string pwd)
        {
            bool retVal = false;
            SqlConnection dbConn = SQLServerUtil.CreateConnection();

            SqlCommand dbcmd = new SqlCommand("SalesVMT_Users_GetInfoByUsersName", dbConn);
            dbcmd.CommandType = CommandType.StoredProcedure;
            dbcmd.Parameters.AddWithValue("@Users_Name", Users_Name);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbcmd.ExecuteReader();
                if (dr.Read())
                {
                    if (pwd == dr["Users_PassWord"].ToString())
                    {
                        retVal = true;
                    }
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
    }
}
